from sqlalchemy.orm import Session
from sqlalchemy import func, distinct, cast, Date, text
from datetime import datetime, timedelta
from typing import Dict, List, Tuple, Any, Optional

from app.models.user import User
from app.models.post import Post
from app.models.campsite import Campsite
from app.models.merchant import Merchant


def get_counts(db: Session) -> Dict[str, int]:
    """获取基本计数统计"""
    user_count = db.query(func.count(User.id)).filter(User.is_active == True).scalar() or 0
    merchant_count = db.query(func.count(Merchant.id)).filter(
        Merchant.is_active == True, 
        Merchant.is_deleted == False
    ).scalar() or 0
    post_count = db.query(func.count(Post.id)).filter(
        Post.is_active == True, 
        Post.is_deleted == False
    ).scalar() or 0
    campsite_count = db.query(func.count(Campsite.id)).filter(
        Campsite.is_active == True, 
        Campsite.is_deleted == False
    ).scalar() or 0
    
    return {
        "user_count": user_count,
        "merchant_count": merchant_count,
        "post_count": post_count,
        "campsite_count": campsite_count
    }


def get_user_trend(db: Session, days: int = 30) -> List[Dict[str, Any]]:
    """获取用户注册趋势"""
    # 计算开始日期
    start_date = (datetime.now() - timedelta(days=days)).date()
    
    # 查询每天的注册用户数
    query = db.query(
        cast(User.created_at, Date).label('date'),
        func.count(User.id).label('count')
    ).filter(
        cast(User.created_at, Date) >= start_date
    ).group_by(
        cast(User.created_at, Date)
    ).order_by(
        cast(User.created_at, Date)
    )
    
    # 执行查询
    result = query.all()
    
    # 转换为字典列表
    return [{"date": str(item.date), "count": item.count} for item in result]


def get_post_trend(db: Session, days: int = 30) -> List[Dict[str, Any]]:
    """获取帖子发布趋势"""
    # 计算开始日期
    start_date = (datetime.now() - timedelta(days=days)).date()
    
    # 查询每天的发布帖子数
    query = db.query(
        cast(Post.created_at, Date).label('date'),
        func.count(Post.id).label('count')
    ).filter(
        cast(Post.created_at, Date) >= start_date,
        Post.is_deleted == False
    ).group_by(
        cast(Post.created_at, Date)
    ).order_by(
        cast(Post.created_at, Date)
    )
    
    # 执行查询
    result = query.all()
    
    # 转换为字典列表
    return [{"date": str(item.date), "count": item.count} for item in result]


def get_active_users(db: Session, days: int = 7, limit: int = 10) -> List[Dict[str, Any]]:
    """获取活跃用户列表"""
    # 计算开始日期
    start_date = datetime.now() - timedelta(days=days)
    
    # 查询发帖最多的用户
    query = db.query(
        User.id,
        User.username,
        User.avatar,
        func.count(Post.id).label('post_count')
    ).join(
        Post, User.id == Post.author_id
    ).filter(
        Post.created_at >= start_date,
        Post.is_deleted == False,
        User.is_active == True
    ).group_by(
        User.id
    ).order_by(
        func.count(Post.id).desc()
    ).limit(limit)
    
    # 执行查询
    result = query.all()
    
    # 转换为字典列表
    return [
        {
            "id": item.id, 
            "username": item.username, 
            "avatar": item.avatar, 
            "post_count": item.post_count
        } 
        for item in result
    ]


def get_popular_campsites(db: Session, limit: int = 10) -> List[Dict[str, Any]]:
    """获取热门营地列表"""
    # 查询关注最多的营地
    query = db.query(
        Campsite.id,
        Campsite.name,
        Campsite.cover_image,
        func.count(distinct(Post.id)).label('post_count')
    ).outerjoin(
        Post, Campsite.id == Post.campsite_id
    ).filter(
        Campsite.is_active == True,
        Campsite.is_deleted == False
    ).group_by(
        Campsite.id
    ).order_by(
        func.count(distinct(Post.id)).desc()
    ).limit(limit)
    
    # 执行查询
    result = query.all()
    
    # 转换为字典列表
    return [
        {
            "id": item.id, 
            "name": item.name, 
            "cover_image": item.cover_image, 
            "post_count": item.post_count
        } 
        for item in result
    ] 